
package com.example.gorannet;

import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteStatement;

import java.util.ArrayList;
import java.util.List;

public class DBHandler
{
    private static final String strRegistrationTable   = "Registration_TBL";

    private static final String strSystemSettingsTable = "System_Settings_TBL";

    private static final String strMessagesTable       = "Messages_TBL";

    private static final String strGCMSettingsTable    = "GCM_Settings_TBL";

    private static final String strTicketsTable        = "Tickets_TBL";

    private static final String strTicketDetailsTable  = "Ticket_details_TBL";

    private static final String strCommentsTable            = "Comments_TBL";


    private static final String strTicketsSurveyTable       = "CS_tickets_survey_TBL";

    private Context             context;

    private SQLiteDatabase      sqLiteDatabase;

    private SQLiteStatement     sqliteStatement;

    private OpenHelper openHelper;

    String ticket_array[] = new String[25];

    private static final String strAuthenticationQuery = "insert into "
            + strRegistrationTable
            + " (username,password,login_count) values (?,?,?)";

    private static final String strGCMSettingsQuery    = "insert into "
            + strGCMSettingsTable
            + " (Sender_id,Server_url) values (?,?)";

    private static final String strMessagesQuery       = "insert into "
            + strMessagesTable
            + " (phone_no, message, time) values (?,?,?)";

    private static final String strTicketsQuery        = "insert into "
            + "tickets_TBL (ticket_id, time, priority,status) values (?,?,?,?)";

    private static final String strTicketDetails        = "insert into "
            + strTicketDetailsTable +" (ticket_id, subject, telephone," +
            " status, priority, createdOn, lastUpdate, neighbourhood, alley," +
            "street, house, address2, site, pairNo, username, password," +
            " user_id, assignedTo, conversation, posted, tctDet, sender, receiver, arrTime) values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";

    private static final String strCommentsInsert    = "insert into "
            + strCommentsTable
            + " (ticket_id, comments, fromTo, isMine, time) values (?,?,?,?,?)";
    private static final String strTicketSurveyInsert    = "insert into "
            + strTicketsSurveyTable
            + " (ticket_no, employee_id, communication, understanding, professionalism, time_to_complete, ticket_date, customer_signature, flag) values (?,?,?,?,?,?,?,?,?)";

    public DBHandler(Context context)
    {
        this.context = context;
        openHelper = new OpenHelper(context);
        sqLiteDatabase = openHelper.getWritableDatabase();
    }

    public long insertAuthentication(String strName, String strNumber, String strLoginCount)
    {
        this.sqliteStatement = sqLiteDatabase.compileStatement(strAuthenticationQuery);
        this.sqliteStatement.bindString(1, strName);
        this.sqliteStatement.bindString(2, strNumber);
        this.sqliteStatement.bindString(3, strLoginCount);
        long longReturnValue = this.sqliteStatement.executeInsert();
        sqliteStatement.close();

        return (longReturnValue);

    }

    public long insertGCMSetting(String sender_id, String server_url)
    {
        this.sqliteStatement = sqLiteDatabase.compileStatement(strGCMSettingsQuery);
        this.sqliteStatement.bindString(1, sender_id);
        this.sqliteStatement.bindString(2, server_url);
        long longReturnValue = this.sqliteStatement.executeInsert();
        sqliteStatement.close();

        return (longReturnValue);
    }

    public long insertGCMMessage(String phone_no, String message, long now)
    {
        if (!phone_no.isEmpty() && phone_no != null)
        {
            this.sqliteStatement = sqLiteDatabase.compileStatement(strMessagesQuery);
            this.sqliteStatement.bindString(1, phone_no);
            this.sqliteStatement.bindString(2, message);
            this.sqliteStatement.bindLong(3, now);
            long longReturnValue = this.sqliteStatement.executeInsert();
            sqliteStatement.close();

            return (longReturnValue);
        }
        return -1L;
    }

    public void updateTicketDetails (String ticket_id, String Status, String priority)
    {
        long now = System.currentTimeMillis();
        sqLiteDatabase.execSQL("update "+ strTicketDetailsTable +" set status= '"+Status+"', priority='"+priority+"', arrTime = "+now+" where ticket_id = "+ticket_id);

    }

    public long insertComments(String strTicketId, String strComment, String fromTo, String isMaine, long time)
    {
        this.sqliteStatement = sqLiteDatabase.compileStatement(strCommentsInsert);
        this.sqliteStatement.bindString(1, strTicketId);
        this.sqliteStatement.bindString(2, strComment);
        this.sqliteStatement.bindString(3, fromTo);
        this.sqliteStatement.bindString(4, isMaine);
        this.sqliteStatement.bindLong(5, time);
        long longReturnValue = this.sqliteStatement.executeInsert();
        sqliteStatement.close();

        return (longReturnValue);
    }

    public long insertTicketSurvey(String strTicketNo, String strEmployeeID, String strCommunication, String strUnderstanding, String strProfessionalism, String strTimeToComplete, Long ticketEndDate, byte[] customerSignature, String strFlag)
    {
        this.sqliteStatement = sqLiteDatabase.compileStatement(strTicketSurveyInsert);

        this.sqliteStatement.bindString(1, strTicketNo);
        this.sqliteStatement.bindString(2, strEmployeeID);
        this.sqliteStatement.bindString(3, strCommunication);
        this.sqliteStatement.bindString(4, strUnderstanding);
        this.sqliteStatement.bindString(5, strProfessionalism);
        this.sqliteStatement.bindString(6, strTimeToComplete);
        this.sqliteStatement.bindLong(7, ticketEndDate);
        this.sqliteStatement.bindBlob(8, customerSignature);
        this.sqliteStatement.bindString(9, strFlag);
        long longReturnValue = this.sqliteStatement.executeInsert();
        sqliteStatement.close();

        return (longReturnValue);
    }

    public String[] getConversations(String ticket_id)
    {
        // Select All Query
        String selectQuery = "select isMine, comments, fromTo, time from  " + strCommentsTable+" where ticket_id = "+ticket_id;

        Cursor cursor = sqLiteDatabase.rawQuery(selectQuery, null);

        String convList[] = new String[cursor.getCount()];
        int counter = 0;
        // looping through all rows and adding to list
        if (cursor.moveToFirst())
        {
            do
            {
                // Adding ticket to list
                convList[counter] = cursor.getString(0)+"@:@"+cursor.getString(1)+"@:@"+cursor.getString(2)+"@:@"+cursor.getString(3);
                counter++;
            }
            while (cursor.moveToNext());

            return (convList);
        }
        else
        {
            convList[0] = "Empty";
            // return contact list

            return (convList);
        }
    }

    public long insertTicketDetails(String detailsArray[])
    {
        this.sqliteStatement = sqLiteDatabase.compileStatement(strTicketDetails);
        this.sqliteStatement.bindString(1, detailsArray[0]);
        this.sqliteStatement.bindString(2, detailsArray[1]);
        this.sqliteStatement.bindString(3, detailsArray[2]);
        this.sqliteStatement.bindString(4, detailsArray[3]);
        this.sqliteStatement.bindString(5, detailsArray[4]);
        this.sqliteStatement.bindString(6, detailsArray[5]);
        this.sqliteStatement.bindString(7, detailsArray[6]);
        this.sqliteStatement.bindString(8, detailsArray[7]);
        this.sqliteStatement.bindString(9, detailsArray[8]);
        this.sqliteStatement.bindString(10, detailsArray[9]);
        this.sqliteStatement.bindString(11, detailsArray[10]);
        this.sqliteStatement.bindString(12, detailsArray[11]);
        this.sqliteStatement.bindString(13, detailsArray[12]);
        this.sqliteStatement.bindString(14, detailsArray[13]);
        this.sqliteStatement.bindString(15, detailsArray[14]);
        this.sqliteStatement.bindString(16, detailsArray[15]);
        this.sqliteStatement.bindString(17, detailsArray[16]);
        this.sqliteStatement.bindString(18, detailsArray[17]);
        this.sqliteStatement.bindString(19, detailsArray[18]);
        this.sqliteStatement.bindString(20, detailsArray[19]);
        this.sqliteStatement.bindString(21, detailsArray[20]);
        String senderAndReceiver [] = detailsArray[18].split("-->");
        String senderName = senderAndReceiver[0];
        String receiverName = senderAndReceiver[1];
        this.sqliteStatement.bindString(22, senderName);
        this.sqliteStatement.bindString(23, receiverName);
        long now = System.currentTimeMillis();
        this.sqliteStatement.bindLong(24, now);


        long longReturnValue = this.sqliteStatement.executeInsert();
        sqliteStatement.close();

        return (longReturnValue);
    }

    public List<String> getTickets()
    {
        List<String> ticketList = new ArrayList<String>();

        // Select All Query
        String selectQuery = "select ticket_id, status from  " + strTicketDetailsTable + " where status = 'Open' OR status = 'On hold'";
        Cursor cursor = sqLiteDatabase.rawQuery(selectQuery, null);
        // looping through all rows and adding to list
        if (cursor.moveToFirst())
        {
            do
            {
                // Adding ticket to list
                ticketList.add(cursor.getString(0)+" ("+cursor.getString(1)+")");
            }
            while (cursor.moveToNext());

            return (ticketList);
        }
        else
        {
            ticketList.add("Empty");
            // return contact list

            return (ticketList);
        }
    }

    public String[] getTicketDetails(String ticket_id)
    {
        String SQL = "select * from " + strTicketDetailsTable + " where ticket_id = "+ticket_id;
        Cursor cursor = sqLiteDatabase.rawQuery(SQL, null);
        if (cursor.moveToFirst())
        {
            ticket_array[0] = cursor.getString(0).toString();
            ticket_array[1] = cursor.getString(1).toString();
            ticket_array[2] = cursor.getString(2).toString();
            ticket_array[3] = cursor.getString(3).toString();
            ticket_array[4] = cursor.getString(4).toString();
            ticket_array[5] = cursor.getString(5).toString();
            ticket_array[6] = cursor.getString(6).toString();
            ticket_array[7] = cursor.getString(7).toString();
            ticket_array[8] = cursor.getString(8).toString();
            ticket_array[9] = cursor.getString(9).toString();
            ticket_array[10] = cursor.getString(10).toString();
            ticket_array[11] = cursor.getString(11).toString();
            ticket_array[12] = cursor.getString(12).toString();
            ticket_array[13] = cursor.getString(13).toString();
            ticket_array[14] = cursor.getString(14).toString();
            ticket_array[15] = cursor.getString(15).toString();
            ticket_array[16] = cursor.getString(16).toString();
            ticket_array[17] = cursor.getString(17).toString();
            ticket_array[18] = cursor.getString(18).toString();
            ticket_array[19] = cursor.getString(19).toString();
            ticket_array[20] = cursor.getString(20).toString();
            ticket_array[21] = cursor.getString(21).toString();
            ticket_array[22] = cursor.getString(22).toString();
            ticket_array[23] = cursor.getString(23).toString();


            return ticket_array;
        }

        return (null);
    }

    public int deleteAllAuthenticationUsers()
    {
        int intReturnValue = sqLiteDatabase.delete(strRegistrationTable, null, null);

        return (intReturnValue);
    }

    public boolean authenticateUser(String strUsername, String strPassword)
    {
        String SQL = "select username,password from " + strRegistrationTable + " where username ='"
                + strUsername + "' and password= '" + strPassword + "'";
        Cursor cursor = sqLiteDatabase.rawQuery(SQL, null);
        if (cursor.moveToFirst())
        {

            return true;
        }

        return false;
    }

    public String getLoginCount(String strUsername, String strPassword)
    {
        String SQL = "select login_count from " + strRegistrationTable + " where username ='"
                + strUsername + "' and password= '" + strPassword + "'";
        Cursor cursor = sqLiteDatabase.rawQuery(SQL, null);
        if (cursor.moveToFirst())
        {
            String loginCount = cursor.getString(0).toString();
            return loginCount;
        }

        return "No such user";
    }

    /* public String lomBinawa(String strUsername, String strPassword)
    {
        String SQL = "select password from " + strRegistrationTable + " where username ='"
                + strUsername+"'";
        Cursor cursor = sqLiteDatabase.rawQuery(SQL, null);
        if (cursor.moveToFirst())
        {

            return cursor.getString(0).toString();
        }

        return "nainm";
    }*/

    public void updateLoginCount (String strLoginCount, String strUserName)
    {
        sqLiteDatabase.execSQL("UPDATE " + strRegistrationTable + " set login_count = '" + strLoginCount
                + "'where username = '"+strUserName+"'");
    }

    public String[] getGCMSettings()
    {
        String SQL = "select Sender_id,Server_url from " + strGCMSettingsTable + " where recordID =1";
        String[] strGCMSettingsArray = new String[2];
        Cursor cursor = sqLiteDatabase.rawQuery(SQL, null);
        if (cursor.moveToFirst())
        {
            strGCMSettingsArray[0] = cursor.getString(0).toString();
            strGCMSettingsArray[1] = cursor.getString(1).toString();

            return strGCMSettingsArray;
        }

        return (null);
    }

    // The Most Recently Added Section
    public List<String> getAllMessagesBetweenTimeframe(long longTimeStart, long longTimeEnd)
    {
        List<String> listMessages = new ArrayList<String>();

        // Select All Query
        String selectQuery = "SELECT  phone_no,message,time FROM " + strMessagesTable
                + " WHERE time between " + longTimeStart + " AND " + longTimeEnd;
        Cursor cursor = sqLiteDatabase.rawQuery(selectQuery, null);
        // looping through all rows and adding to list
        if (cursor.moveToFirst())
        {
            do
            {
                String strTemp = cursor.getString(0) + "\n" + cursor.getString(1) + "\n"
                        + cursor.getString(2);
                // Adding contact to list
                listMessages.add(strTemp);
            }
            while (cursor.moveToNext());

            return (listMessages);
        }
        else
        {
            listMessages.add("Empty");
            // return contact list

            return (listMessages);
        }
    }

    // Returning The Ticket Numbers
    public List<String> getAllTickets()
    {
        List<String> listTickets = new ArrayList<String>();

        // Select All Query
        String selectQuery = "SELECT  * FROM " + strTicketsTable+" where status=0 or status=2";
        Cursor cursor = sqLiteDatabase.rawQuery(selectQuery, null);

        // looping through all rows and adding to list
        if (cursor.moveToFirst())
        {
            do
            {
                String strTemp = cursor.getString(0);

                // Adding contact to list
                listTickets.add(strTemp);
            }
            while (cursor.moveToNext());
        }
        else
        {
            listTickets.add("Empty");
        }

        // return contact list

        return listTickets;
    }

    public void updateMessage(String phoneNo, String strMessage, long longTimeStamp)
    {
        sqLiteDatabase.execSQL("UPDATE " + strMessagesTable + " set message = '" + strMessage
                + "', time = " + longTimeStamp+"where phone_no = '"+phoneNo+"'");

    }
}
